﻿using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using VOL.Core.Controllers.Basic;
using VOL.Core.DBManager;
using VOL.Core.Extensions;
using VOL.Core.ManageUser;
using VOL.Core.Services;
using VOL.Core.Utilities;
using VOL.Entity.DomainModels;
using syio = System.IO;
using sysnet = System.Net;
using System.Web;
/// <summary>
/// 自定义api调用
/// </summary>
namespace VOL.WebApi.Controllers.MyTable.customer_api
{
    [Route("api/customer_jr_mobile")]
    [ApiController]
    public class customer_jr_mobile : Controller
    {
        /// <summary>
        ///获取订单信息
        /// </summary>
        /// <returns></returns>
        [HttpPost, Route("get_jr_saleorder")]
        public object get_jr_saleorder(string options)
        {
            dynamic queryjson = JsonConvert.DeserializeObject(options);
            Hashtable rtn_hst = new Hashtable();
            dynamic wheres = queryjson.wheres;
            dynamic orderby = queryjson.orderby;
            int currentpage = queryjson.currentpage;//当前页
            int pagesize = queryjson.pagesize;//每页条数
            string where_str = "";
            string order_str = "";
            string ordername = "CreateDate";//默认排序字段
            string orderfx = "desc";

            //Console.WriteLine(queryjson);
            if (((JObject)wheres).Count != 0)//判断查询条件不为空
            {
                wheres = JsonConvert.SerializeObject(wheres);//把json对象转为字符串，序列化
                JObject obj = JObject.Parse(wheres);
                foreach (var item in obj)
                {
                    if (item.Key == "")//如果需要查询的话，填具体的字段
                    {
                        where_str = where_str + " and " + item.Key + " like '%" + item.Value + "%'";
                    }

                    if (item.Key == "reim_id")//如果需要查询的话，填具体的字段
                    {
                        where_str = where_str + " and " + item.Key + " in(" + item.Value + ")";
                    }
                    // Console.WriteLine("{0}:{1}",item.Key,item.Value);//用于调试,查看条件
                }

            }
            //Console.WriteLine(queryjson.orderby);
            if (((JObject)orderby).Count != 0)//判断排序不为空
            {
                //判断字符串是否为空，把转换为长度判断
                if (Convert.ToString(orderby.ordername).Length > 0) ordername = orderby.ordername;
                if (Convert.ToString(orderby.orderfx).Length > 0) orderfx = orderby.orderfx;

            }
            order_str = " order by " + ordername + " " + orderfx;
            string sql = "select derive_material_code,(select material_name from TM_VHCL_MATERIAL where material_code=derive_material_code) mat_name,chassis_remark chassis_remark,to_char(check_date, 'yyyy-mm-dd') check_date ,b.delivery_address,b.ORDER_NO,(select dealer_shortname from tm_dealer where dealer_id = b.dealer_id) dealer_shortname,b.order_quantity,b.ORDER_AMOUNT from TT_ORDER_CHECK a, TT_SALES_ORDER b where a.order_id = b.order_id and check_status = 10491005  and to_char(check_date, 'yyyymm') >= TO_CHAR(TRUNC(SYSDATE,'year'),'yyyymm') and to_char(check_date, 'yyyymm')<=TO_CHAR(TRUNC(SYSDATE,'year'),'yyyymm')+11  and b.ORDER_AMOUNT>0 order by check_date desc";
            sql = sql.Replace("'", "''");
            List<dynamic> rnt_list = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery(DMS,'" + sql + "')", "").ToList(); ;//订单明细,通过dblink取数，SQL运行的是oracle预警

            rtn_hst.Add("rows", rnt_list.Count());

            rnt_list = rnt_list.Skip(pagesize * (currentpage - 1)).Take(pagesize).ToList();
            rtn_hst.Add("_query", rnt_list);
            JsonSerializerSettings js = new JsonSerializerSettings();
            js.DateFormatString = "yyyy-MM-dd HH:mm:ss";
            return Json(rtn_hst, js);//原样返回数据,系统默认为小驼峰方式
        }

        [HttpPost, HttpGet, Route("get_shuj")]
        public object get_shuj()
        {
            Hashtable rtn_hst = new Hashtable();
            string sql = "";
            //当前生产状态
            sql = "  select tt2.order_state models, nvl(tt1.co, 0) co  from (select order_state, ym, count(*) co from (select substr(YEAR_M, 1, 4) ym, YEAR_M, order_state   from VW_ORDERVIEW_JEEFW  where substr(YEAR_M, 1, 4) = to_char(sysdate,'yyyy')) tt  group by order_state, ym) tt1,  (select state order_state,orderid from JEEFW_ORDERCYCLE_STATE) tt2  where tt2.order_state = tt1.order_state(+) order by orderid asc";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> k_type_bl = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([dms],'" + sql + "')", "").ToList();

            sql = "select  numa,dat from (select sum(b.ORDER_QUANTITY) - sum(nvl(b.cancel_Quantity, 0)) numa, to_char(check_date, 'yyyymm') dat from TT_ORDER_CHECK a, TT_SALES_ORDER b where a.order_id = b.order_id and check_status = 10491005  group by to_char(check_date, 'yyyymm') order by to_char(check_date, 'yyyymm') desc ) tab1 where rownum <= 6 order by dat ";
            sql = sql.Replace("'", "''");
            List<dynamic> jdqs = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery(DMS,'" + sql + "')", "").ToList(); ;//接单趋势图,通过dblink取数，SQL运行的是oracle预警

            rtn_hst.Add("k_type_bl", k_type_bl);//生产执行
            rtn_hst.Add("jdqs", jdqs);//销售接单
            return rtn_hst;
        }

        /// <summary>
        /// 生产相关统计数据
        /// </summary>
        /// <returns></returns>
        [HttpPost, HttpGet, Route("product_about")]
        public object product_about()
        {
            Hashtable rtn_hst = new Hashtable();
            //本年累计生产下线
            string sql = "  select substr(proddate, 1, 4) dat, count(distinct vin) sum from prodplan t where offmark = '*' and to_char(sysdate, 'yyyy') = substr(proddate, 1, 4)  and lineno = '100' group by substr(proddate, 1, 4) order by substr(proddate, 1, 4) asc";
            sql = sql.Replace("'", "''");
            List<dynamic> y_off = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList(); ;//本年累计下线
            //Console.WriteLine(GET_SQL_TXT_FROM_DB("8"));

            //本月生产计划和每日生产计划
            sql = " select substr(proddate,1,8) dat,NVL(sum(num),0) sum from seqplan t where to_char(sysdate,'yyyymm')=substr(proddate,1,6) and lineno in(100) group by substr(proddate,1,8),lineno order by substr(proddate,1,8)";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> m_plan = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList(); ;//本月生产计划和每日生产计划
            int m_total_plan = m_plan.Sum(t => int.Parse(t.SUM));//注意要大写
            //string max_dat = m_plan.Max(m => m.DAT);//获取最大日期 即为当前日期
            List<dynamic> d_plan = m_plan.Where(w => w.DAT == DateTime.Now.ToString("yyyyMMdd")).ToList();//查询到当前日期对应的生产计划
            int curr_day_plan = 0;
            if (d_plan.Count > 0) curr_day_plan = int.Parse(d_plan[0].SUM);
            // Console.WriteLine(DateTime.Now.ToString("yyyyMMdd"));


            //本月、今日上线
            sql = " select substr(ontime,1,8) dat,count(*) sum from prodplan where to_char(sysdate,'yyyymm')=substr(ontime,1,6) and lineno in(100) and onmark='*' group by substr(ontime,1,8) order by substr(ontime,1,8)";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> m_on = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList();
            int m_total_on = m_on.Sum(t => int.Parse(t.SUM));//注意要大写
            //string max_dat = m_on.Max(m => m.DAT);//获取最大日期 即为当前日期
            List<dynamic> d_on = m_on.Where(w => w.DAT == DateTime.Now.ToString("yyyyMMdd")).ToList();//查询到当前日期对应的生产计划
            int curr_day_on = 0;
            if (d_on.Count > 0) curr_day_on = int.Parse(d_on[0].SUM);

            //本月、今日下线
            sql = "select substr(OFFtime,1,8) dat,count(distinct vin) sum from prodplan t where offmark='*' and to_char(sysdate,'yyyymm')=substr(OFFtime,1,6) and lineno='100' group by substr(OFFtime, 1, 8) order by substr(OFFtime, 1, 8) asc";
            sql = sql.Replace("'", "''");//单引号转义
            List<dynamic> m_off = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + sql + "')", "").ToList();
            int m_total_off = m_off.Sum(t => int.Parse(t.SUM));//注意要大写
            //string max_dat = m_off.Max(m => m.DAT);//获取最大日期 即为当前日期
            List<dynamic> d_off = m_off.Where(w => w.DAT == DateTime.Now.ToString("yyyyMMdd")).ToList();//查询到当前日期对应的生产计划
            int curr_day_off = 0;
            if (d_off.Count > 0) curr_day_off = int.Parse(d_off[0].SUM);

            rtn_hst.Add("y_off", y_off);//本年累计下线
            rtn_hst.Add("m_total_plan", m_total_plan);//本月总生产计划
            rtn_hst.Add("curr_day_plan", curr_day_plan);//当日生产计划

            rtn_hst.Add("m_total_on", m_total_on);//本月总上线数量
            rtn_hst.Add("curr_day_on", curr_day_on);//当日上线数量

            rtn_hst.Add("m_total_off", m_total_off);//本月总下线数量
            rtn_hst.Add("curr_day_off", curr_day_off);//当日下线数量
            return rtn_hst;
        }

        /// <summary>
        /// 导出Excel,使用文件流下载文件
        /// </summary>
        /// <returns></returns>
        [HttpPost, Route("down_bytes_file"), AllowAnonymous]
        public ActionResult down_bytes_file()
        {
            
            string path = null;
            //string fileName = null;

            // fileName = "temp.xlsx";//随便给
            // path = ("wwwroot").MapPath()+ "/Upload/Tables/Fk_reim/202204261017139334/高端重卡数字化智能工厂改造项目-IT部分.docx";
            path = ("wwwroot").MapPath() + "/Upload/Tables/Fk_reim/202204260908538689/QQ截图20220426090834.png";
            //path = ("wwwroot").MapPath() + "/Upload/Tables/Fk_reim/202204271109487604/居家工作日志.xlsx";

            //return path;
            /* var stream = new syio.FileStream(path, syio.FileMode.Open);

             HttpResponseMessage response = new HttpResponseMessage(sysnet.HttpStatusCode.OK);
             response.Content = new StreamContent(stream);
             response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
             response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
             {
                 FileName = "高端重卡数字化智能工厂改造项目-IT部分.docx"
             };
             stream.Close();
             return response;*/
            //var contentType = MimeMapping.GetMimeMapping("QQ截图20220426090834.png");
            return File(syio.File.ReadAllBytes(path), sysnet.Mime.MediaTypeNames.Application.Octet, "QQ截图20220426090834.png");
           // return path;
        }

    }
}
